﻿using System;
using System.Collections.Generic;
using System.Configuration;
using System.Diagnostics;
using System.Globalization;
using System.IO;
using System.Linq;
using System.Reflection;
using System.ServiceModel;
using System.ServiceModel.Activation;
using System.Web;
using QuailDb;
using RemoteControlCapability = QuailDb.RemoteControlCapability;

namespace QuailDashboard.View.Web
{
    // NOTE: If you change the class name "QuailDataService" here, you must also update the reference to "QuailDataService" in Web.config.
    /// <summary>
    /// The Quail web service
    /// </summary>
    [AspNetCompatibilityRequirements(RequirementsMode = AspNetCompatibilityRequirementsMode.Required)]
    public class QuailDataService : IQuailDataService, IDisposable
    {
        private QuailObjectContextConnection _dbContext;
        private QuailDataContext _db;

        /// <summary>
        /// Initializes a new instance of the <see cref="QuailDataService"/> class.
        /// </summary>
        public QuailDataService()
        {
            _dbContext = new QuailObjectContextConnection();
            _db = _dbContext.Context;
        }

        ~QuailDataService()
        {
            Dispose(false);
        }

        /// <summary>
        /// calls Dispose(true)
        /// </summary>
        public void Dispose()
        {
            Dispose(true);
            GC.SuppressFinalize(this);
        }

        /// <summary>
        /// Disposes the database connection
        /// </summary>
        /// <param name="disposing"></param>
        protected virtual void Dispose(bool disposing)
        {
            if (disposing)
            {
                _db = null;

                if (_dbContext != null)
                {
                    _dbContext.Dispose();
                    _dbContext = null;
                }
            }
        }

        /// <summary>
        /// Gets the current remote control status
        /// </summary>
        /// <returns>a list of remote control status</returns>
        public List<RemoteControlStatus> GetCurrentRemoteControlStatus()
        {
            Logger.Log().Info(string.Format("{0}", MethodBase.GetCurrentMethod().Name));
            var allrows = from rows in _db.RemoteControlStatus
                          orderby rows.IsAvailable descending, rows.HostName ascending
                          select rows;

            return allrows.ToList();
        }


        /// <summary>
        /// Gets a test run task for a given TestRunTaskId.
        /// </summary>
        /// <param name="testRunTaskId">Int TestRunTaskId</param>
        /// <returns>The TestRunTask</returns>
		public TestRunTask GetTestRunTaskById(long testRunTaskId)
        {
            return _db.TestRunTasks
                .FirstOrDefault(r => r.TestRunTaskId == testRunTaskId);
        }


        /// <summary>
        /// Gets a list of test run tasks for a given testrunid and parentid
        /// </summary>
        /// <param name="testrunid">the test run id</param>
		/// <param name="parenttestruntaskid">the parent id</param>
        /// <returns>the list of tasks</returns>Jkasqwui!
		public List<TestRunTask> GetTestRunTask(int testrunid, long parenttestruntaskid)
        {
            Logger.Log().Info(string.Format("{0}", MethodBase.GetCurrentMethod().Name));

            const int maxRows = 7500;

            var allrows = _db.TestRunTasks
				.Where(r => r.TestRunId == testrunid && r.ParentTestRunTaskId == parenttestruntaskid)
                .OrderBy(r => r.TaskStartDate)
                .ThenBy(r => r.TestRunTaskId)
                .ToList();

            if (allrows.Count > maxRows)
            {
                var count = allrows.Count - maxRows;
                while (allrows.Count > maxRows)
                    allrows.RemoveAt(maxRows);
                allrows.Add(new TestRunTask
                                {
									ParentTestRunTaskId = parenttestruntaskid,
                                    TaskFinishDate = allrows[maxRows - 1].TaskStartDate,
                                    TaskName = "WARNING: " + count + " tasks are truncated from display!",
                                    TaskStartDate = allrows[maxRows - 1].TaskStartDate,
                                    TaskStatusId = 3, // LogStatus.Warning,
                                    TestRunId = testrunid,
                                    TestRunTaskId = -1
                                });
            }

            return allrows.ToList();
        }

        /// <summary>
        /// Gets all the attributes for a test run task
        /// </summary>
        /// <param name="testruntaskid">The TestRunTaskID</param>
        /// <returns></returns>
        public List<TestRunTaskAttribute> GetTestRunTaskAttribute(long testruntaskid)
        {
            Logger.Log().Info(string.Format("{0}", MethodBase.GetCurrentMethod().Name));

            var allrows = from rows in _db.TestRunTaskAttributes
                          where rows.TestRunTaskId == testruntaskid
                          orderby rows.AttributeName
                          select rows;

            return allrows.ToList();            
        }

        /// <summary>
        /// Gets All the log entries for a test run task
        /// </summary>
        /// <param name="testruntaskid">The TestRunTaskID</param>
        /// <returns></returns>
		public List<TestRunTaskLogEntry> GetTestRunTaskLogEntry(long testruntaskid)
        {
            Logger.Log().Info(string.Format("{0}", MethodBase.GetCurrentMethod().Name));
            var allrows = from rows in _db.TestRunTaskLogEntries
                          where rows.TestRunTaskId == testruntaskid
                          orderby rows.EntryCreateDate, rows.TestRunTaskLogEntryId
                          select rows;

            return allrows.ToList();            
        }

        /// <summary>
        /// Gets all the Log Entries for a given testrunid
        /// </summary>
        /// <param name="testrunid"></param>
        /// <returns></returns>
        public List<TestRunTaskLogEntry> GetAllTestRunTaskLogEntry(int testrunid)
        {
            Logger.Log().Info(string.Format("{0}", MethodBase.GetCurrentMethod().Name));
            // for now, return only warning/fail entries
            var allrows = from entries in _db.TestRunTaskLogEntries
                          from tasks in _db.TestRunTasks
                          where tasks.TestRunId == testrunid &&
                                tasks.TestRunTaskId == entries.TestRunTaskId &&
                                (entries.EntryStatusId == 2 ||
                                 entries.EntryStatusId == 4 ||
                                 entries.EntryStatusId == 11)
                          orderby entries.EntryCreateDate, entries.TestRunTaskLogEntryId
                          select entries;

            return allrows.ToList();
        }

        /// <summary>
        /// Gets a list of remote control hosts
        /// </summary>
        /// <returns></returns>
        public List<RemoteControlHostList> GetRemoteControlHostList()
        {
            Logger.Log().Info(string.Format("{0}", MethodBase.GetCurrentMethod().Name));
            var allrows = from rows in _db.RemoteControlHostLists
                          orderby rows.HostName
                          select rows;

            return allrows.ToList();                        
        }

        /// <summary>
        /// Gets the specified remote control host
        /// </summary>
        /// <param name="hostname">The host name</param>
        /// <returns></returns>
        public RemoteControlHostList GetRemoteControlHost(string hostname)
        {
            Logger.Log().Info(string.Format("{0}", MethodBase.GetCurrentMethod().Name));
            return _db.RemoteControlHostLists.FirstOrDefault(h => h.HostName == hostname);
        }


        /// <summary>
        /// Adds a remote control host to the system (obsolete: use ConfigureRemoteControlWithMaxUsage instead)
        /// </summary>
        /// <param name="hostname">The host name</param>
        /// <param name="port">The port</param>
        /// <param name="notes">Optional notes for the host</param>
        public void AddRemoteControl(string hostname, int port, string notes)
        {
            Logger.Log().Info(string.Format("{0}", MethodBase.GetCurrentMethod().Name));
            var allrows = from rows in _db.RemoteControlHostLists
                          where rows.HostName == hostname
                          select rows;

            var didone = false;

            foreach (var row in allrows)
            {
                row.Notes = notes;
                if (row.HostPort != port)
                    row.HostPort = port;
                didone = true;
            }

            if (!didone)
            {
                var newhost = new RemoteControlHostList
                {
                    HostName = hostname,
                    HostPort = port,
                    IsAvailable = true,
                    Notes = notes,
                    MaxUsageCount = null
                };

                _db.RemoteControlHostLists.InsertOnSubmit(newhost);
            }

            _db.SubmitChanges();
        }

        /// <summary>
        /// Enables or disables a remote control host (obsolete: use ConfigureRemoteControlWithMaxUsage instead)
        /// </summary>
        /// <param name="hostname">The hostname</param>
        /// <param name="enable"></param>
        /// <returns></returns>
        public bool EnableRemoteControl(string hostname, bool enable)
        {
            Logger.Log().Info(string.Format("{0}", MethodBase.GetCurrentMethod().Name));
            var allrows = from rows in _db.RemoteControlHostLists
                          where rows.HostName == hostname
                          select rows;

            bool didone = false;

            foreach (var row in allrows)
            {
                row.IsAvailable = enable;
                didone = true;
            }

            if (!didone)
                return false;

            _db.SubmitChanges();
            return true;
        }

        /// <summary>
        /// Configures a new or existing remote control host (obsolete: use ConfigureRemoteControlWithMaxUsage instead)
        /// </summary>
        /// <param name="hostname">The hostname</param>
        /// <param name="port">The port</param>
        /// <param name="enable">A flag to enable or disable the host</param>
        /// <returns>True if the configuration was made.</returns>
        public bool ConfigureRemoteControl(string hostname, int port, bool enable)
        {
            Logger.Log().Info(string.Format("{0}", MethodBase.GetCurrentMethod().Name));
            if (enable)
                _db.AddRemoteControlHost(hostname, port, null);
            else
                _db.RemoveRemoteControlHost(hostname);

            return true;
        }

        /// <summary>
        /// Configures a new or existing remote control host.
        /// </summary>
        /// <param name="hostname">The hostname</param>
        /// <param name="port">The port</param>
        /// <param name="enable">A flag to enable or disable the host</param>
        /// <param name="maxUsageCount">The maximum number of simultaneous requests the host can handle.  NULL or zero = Unbounded</param>
        /// <returns>True if the configuration was made.</returns>
        public bool ConfigureRemoteControlWithMaxUsage(string hostname, int port, bool enable, int maxUsageCount)
        {
            Logger.Log().Info(string.Format("{0}", MethodBase.GetCurrentMethod().Name));
            if (enable)
                _db.AddRemoteControlHost(hostname, port, maxUsageCount);
            else
                _db.RemoveRemoteControlHost(hostname);

            return true;
        }

        /// <summary>
        /// Sets the capabilities for a given remote control host
        /// </summary>
        /// <param name="hostname">The hostname</param>
        /// <param name="capabilities">List of capabilities</param>
        /// <returns>
        /// True if the set was successful, falst otherwise
        /// </returns>
        public bool SetRemoteControlCapabilities(string hostname, List<string> capabilities)
        {
            Logger.Log().Info(string.Format("{0}", MethodBase.GetCurrentMethod().Name));
            var host = _db.RemoteControlHostLists.Where(rchl => rchl.HostName == hostname).FirstOrDefault();
            if (host == null)
                return false;

            var caps = _db.RemoteControlHostCapabilities.Where(rchc => rchc.RemoteControlHostListId == host.RemoteControlHostListId);

            foreach (var cap in caps)
            {
                var capcode = cap.RemoteControlCapabilityCode.ToUpper();

                var found = false;
                var idx = 0;
                while (idx < capabilities.Count)
                {
                    if (string.Compare(capabilities[idx], capcode, true) == 0)
                    {
                        capabilities.RemoveAt(idx);
                        found = true;
                        break;
                    }
                    idx++;
                }

                if (!found)
                    _db.RemoteControlHostCapabilities.DeleteOnSubmit(cap);
            }

            capabilities.ForEach(newcap =>
                                     {
                                         if (!_db.RemoteControlCapabilities.Any(rcc => rcc.RemoteControlCapabilityCode == newcap))
                                             _db.RemoteControlCapabilities.InsertOnSubmit(new RemoteControlCapability
                                                                                              {
                                                                                                  Description = newcap,
                                                                                                  RemoteControlCapabilityCode
                                                                                                      = newcap
                                                                                              });

                                         _db.RemoteControlHostCapabilities.InsertOnSubmit(new RemoteControlHostCapability
                                                                                              {
                                                                                                  RemoteControlCapabilityCode = newcap,
                                                                                                  RemoteControlHostListId = host.RemoteControlHostListId
                                                                                              });
                                     });

            _db.SubmitChanges();

            return true;
        }

        /// <summary>
        /// Gets a binary file by name
        /// </summary>
        /// <param name="filename">The filename</param>
        /// <returns>The Blob for the file</returns>
        public Blob GetBinaryFile(string filename)
        {
            Logger.Log().Info(string.Format("{0}", MethodBase.GetCurrentMethod().Name));
            switch (filename)
            {
                case "selenium-server.jar":
                case "user-extensions.js":
                case "seleniumfirefoxprofile.zip":
                case "hosts":
                    break;
                default:
                    filename = null;
                    break;
            }

            if (string.IsNullOrEmpty(filename))
                return new Blob(null);

            using (
                var stream =
                    new FileStream(
                        HttpContext.Current.Server.MapPath(
                            ConfigurationManager.AppSettings["SeleniumContentFolder"])
                        + "\\" + filename,
                        FileMode.Open,
                        FileAccess.Read,
                        FileShare.Read))
            {
                using (var reader = new BinaryReader(stream))
                    return new Blob(reader.ReadBytes((int) stream.Length));
            }
        }

        /// <summary>
        /// Gets a dictionary mapping each recently used data group to a list of num test / num thread pairs that it's been run with
        /// </summary>
        /// <returns>the dictionary</returns>
        public Dictionary<string, List<Tuple<int?, int?>>> GetTestsAndThreadsByGroup()
        {

            Logger.Log().Info(string.Format("{0}", MethodBase.GetCurrentMethod().Name));
            var olddate = DateTime.Now.AddDays(-15);
            var results = _db.LoadTests.Where(lt => lt.StartDate > olddate)
                .Where(lt => lt.NumTests != null)
                .Where(lt => lt.NumThreads != null)
                .Join(_db.LoadTestUrls, lt => lt.LoadTestId, ltu => ltu.LoadTestId, (lt, ltu) => new {lt.NumTests, lt.NumThreads, ltu.LoadTestDataId})
                .Join(_db.LoadTestDatas, lt => lt.LoadTestDataId, ltd => ltd.LoadTestDataId, (lt, ltd) => new { lt.NumTests, lt.NumThreads, ltd.DataGroup})
                .Select(lt => new { lt.NumTests, lt.NumThreads, lt.DataGroup })
                .Distinct().ToList();

            var testsAndThreadsByGroup = new Dictionary<string, List<Tuple<int?, int?>>>();
            foreach (var result in results)
            {
                if (!testsAndThreadsByGroup.ContainsKey(result.DataGroup))
                    testsAndThreadsByGroup[result.DataGroup] = new List<Tuple<int?, int?>>();
                testsAndThreadsByGroup[result.DataGroup].Add(new Tuple<int?, int?>(result.NumTests, result.NumThreads));
            }

            return testsAndThreadsByGroup;
        }

        /// <summary>
        /// Gets a list of recently used numbers of tests and threads in load tests
        /// </summary>
        /// <returns>A list of tuples, each representing a number of tests and a number of threads</returns>
        public List<Tuple<int?, int?>> GetTestsAndThreads()
        {
            Logger.Log().Info(string.Format("{0}", MethodBase.GetCurrentMethod().Name));
            var olddate = DateTime.Now.AddDays(-15);
            var result = _db.LoadTests.Where(lt => lt.StartDate > olddate)
                .Where(lt => lt.NumTests != null)
                .Where(lt => lt.NumThreads != null)
                .Select(lt => new {lt.NumTests, lt.NumThreads})
                .Distinct().ToList();
            return result.Select(lt => new Tuple<int?, int?>(lt.NumTests, lt.NumThreads)).ToList();
        }

        /// <summary>
        /// Gets a list of recently used test environments
        /// </summary>
        /// <returns>A list of environments</returns>
        public List<TestEnvironment> GetEnvironments()
        {
            Logger.Log().Info(string.Format("{0}", MethodBase.GetCurrentMethod().Name));
            var olddate = DateTime.Now.AddDays(-10);
            List<TestEnvironment> list =  GetEnvironmentRows()
                .Where(te => te.LastUsedDate > olddate)
                .ToList();
            //List<TestEnvironment> list = new List<TestEnvironment>();
            //list.Add(new TestEnvironment() { Description = "description", EnvironmentName = "asfasf", LastUsedDate = DateTime.Now, TestEnvironmentId = 1 });
            return list;
        }

        /// <summary>
        /// Gets a list of all test environments
        /// </summary>
        /// <returns>A list of environments</returns>
        public List<TestEnvironment> GetAllEnvironments()
        {
            Logger.Log().Info(string.Format("{0}", MethodBase.GetCurrentMethod().Name));
            return GetEnvironmentRows().ToList();
        }

        /// <summary>
        /// Gets a TestEnvironment object by environment name
        /// </summary>
        /// <param name="name">The environment name</param>
        /// <returns>A TestEnvironment object or null</returns>
        public TestEnvironment GetEnvironment(string name)
        {
            Logger.Log().Info(string.Format("{0}", MethodBase.GetCurrentMethod().Name));
            var allrows = from rows in _db.TestEnvironments
                          where rows.EnvironmentName == name
                          orderby rows.Description
                          select rows;

            var result = allrows.FirstOrDefault();

        	return result;
        }

		public List<ViewTestLibraryRun> GetTestLibraryRuns(int? testLibraryId, int testEnvironmentId, DateTime? minDateTime, DateTime? maxDateTime)
		{
			Logger.Log().Info(string.Format("{0}", MethodBase.GetCurrentMethod().Name));

			var q = _db.ViewTestLibraryRuns
				.Where(tlr => tlr.TestEnvironmentId == testEnvironmentId);

			if (testLibraryId.HasValue)
				q = q.Where(tlr => tlr.TestLibraryId == testLibraryId.Value);

			if (minDateTime.HasValue)
				q = q.Where(tlr => tlr.StartDate >= minDateTime.Value);

			if (maxDateTime.HasValue)
				q = q.Where(tlr => tlr.StartDate < maxDateTime.Value);

			return q.ToList();
		}

		public ViewTestLibraryRun GetMostRecentTestLibraryRun(int testLibraryId, int testEnvironmentId)
		{
			return
			_db.ViewTestLibraryRuns
				.Where(tlr => tlr.TestLibraryId == testLibraryId && tlr.TestEnvironmentId == testEnvironmentId)
				.OrderByDescending(tlr => tlr.StartDate)
				.FirstOrDefault();
		}

		public ViewTestLibraryRun GetTestLibraryRun(int testLibraryRunId)
		{
			return
				_db.ViewTestLibraryRuns
					.FirstOrDefault(tlr => tlr.TestLibraryRunId == testLibraryRunId);
		}

		public List<ViewTestRun> GetTestRunsForTestLibraryRun(int testLibraryRunId)
		{
			return
				_db.ViewTestRuns
					.Where(tr => tr.TestLibraryRunId == testLibraryRunId)
					.ToList();
		}

		public List<TestClassName> GetTestNamesForTestLibrary(int testLibraryId)
		{
			return
				_db.ViewTestClassNamesForLibraries
					.Where(tc => tc.TestLibraryId == testLibraryId)
					.ToList()
					.Select(tc => new TestClassName
					              	{
					              		ClassName = tc.TestClassName.Substring(tc.TestClassName.IndexOf('.') + 1),
					              		TestName = tc.TestName
					              	})
					.ToList();
		}

        /// <summary>
        /// Gets a list of all test libraries
        /// </summary>
        /// <returns>A list of TestLibrary objects</returns>
        public List<TestLibrary> GetLibraries()
        {
            Logger.Log().Info(string.Format("{0}", MethodBase.GetCurrentMethod().Name));
            var allrows = from rows in _db.TestLibraries
                          orderby rows.Description
                          select rows;

            return allrows.ToList();
        }

        /// <summary>
        /// Gets a list of all test class names
        /// </summary>
        /// <returns>A list of ViewTestClassName</returns>
        public List<ViewTestClassName> GetTestClassNames()
        {
            Logger.Log().Info(string.Format("{0}", MethodBase.GetCurrentMethod().Name));
            var allrows = from rows in _db.ViewTestClassNames
                          select rows;

            return allrows.ToList();
        }

        /// <summary>
        /// Gets a list of Task Statuses
        /// </summary>
        /// <returns>A list of TaskStatus</returns>
        public List<TaskStatus> GetTaskStatuses()
        {
            Logger.Log().Info(string.Format("{0}", MethodBase.GetCurrentMethod().Name));
            List<TaskStatus> list = _db.TaskStatus
                .OrderBy(ts => ts.Ordinal)
                //.Select(ts => new TaskStatus { Ordinal = ts.Ordinal, TaskStatus1 = ts.TaskStatus1, TaskStatusId = ts.TaskStatusId })
                .ToList();

            //List<TaskStatus> list = (from ts in _db.TaskStatus
            //              orderby ts.Ordinal
            //              select new TaskStatus { Ordinal=ts.Ordinal, TaskStatus1=ts.TaskStatus1, TaskStatusId=ts.TaskStatusId }).ToList();

            //var allrows = from rows in _db.TaskStatus
            //              orderby rows.Ordinal
            //              select rows;

            //return allrows.ToList();  

            //List<TaskStatus> list = new List<TaskStatus>();
            //list.Add(new TaskStatus() { TaskStatusId=1, TaskStatus1="pass"});
            return list;
        }

        /// <summary>
        /// Gets the run status for a given environment and test classname
        /// </summary>
        /// <param name="testenvironmentid">the environment</param>
        /// <param name="testclassname">the test class name</param>
        /// <returns>A list of GetRunStatusResults</returns>
        public List<GetRunStatusResult> GetRunStatus(int testenvironmentid, string testclassname)
        {
            Logger.Log().Info(string.Format("{0}", MethodBase.GetCurrentMethod().Name));
            return GetRunStatusForForTestAndDate(testenvironmentid, testclassname, null);
        }

        public List<GetRunStatusResult> GetRunStatusForDate(int testenvironmentid, DateTime dt)
        {
            Logger.Log().Info(string.Format("{0}", MethodBase.GetCurrentMethod().Name));
            return GetRunStatusForForTestAndDate(testenvironmentid, null, dt);
        }

        public List<GetRunStatusResult> GetRunStatusForForTestAndDate(int testenvironmentid, string testclassname, DateTime? dt)
        {
            Logger.Log().Info(string.Format("{0}", MethodBase.GetCurrentMethod().Name));

            var sw = new Stopwatch();
            sw.Restart();
            var allrows =
                _db.GetRunStatus(testenvironmentid, testclassname, dt)
                    .ToList();

            var result = allrows.OrderByDescending(r => r.RunStartDate).ToList();

            var time = sw.ElapsedMilliseconds;

            return result;
        }

        public List<GetRunStatusResult> GetRunStatusForTestsAndDate(int testenvironmentid, string[] testclassname, DateTime? dt)
        {
            Logger.Log().Info(string.Format("{0}", MethodBase.GetCurrentMethod().Name));
            var result = new List<GetRunStatusResult>();

            foreach (var t in testclassname)
            {
                var allrows = from rows in _db.GetRunStatus(testenvironmentid, t, dt)
                              orderby rows.RunStartDate descending
                              select rows;

                var row = allrows.FirstOrDefault();
                if (row != null)
                    result.Add(row);
            }

            return result;
        }

        public List<SearchTestRunTaskResult> SearchForTestRunTask(int testrunid, long? testruntaskid, bool searchascending, string searchtext, string tasktatusidlist)
        {
            Logger.Log().Info(string.Format("{0}", MethodBase.GetCurrentMethod().Name));
            long? foundtestruntaskid = null;
            var allrows = from rows in _db.SearchTestRunTask(testrunid, testruntaskid, searchascending, searchtext, tasktatusidlist, ref foundtestruntaskid)
                          select rows;

            return allrows.ToList();  
        }

        public ViewTestRun GetTestRun(int testrunid)
        {
            Logger.Log().Info(string.Format("{0}", MethodBase.GetCurrentMethod().Name));
            var allrows = from rows in _db.ViewTestRuns
                          where rows.TestRunId == testrunid
                          select rows;

            return allrows.FirstOrDefault();
        }

        public TestNote AddTestNote(int testid, string userid, string note)
        {
            Logger.Log().Info(string.Format("{0}", MethodBase.GetCurrentMethod().Name));
            var row = new TestNote
                          {
                              CreatedBy = userid,
                              CreateDate = DateTime.Now,
                              Note = note,
                              TestId = testid,
                              Active = true,
                          };

            _db.TestNotes.InsertOnSubmit(row);
            _db.SubmitChanges();

            return row;
        }

        public void ActivateTestNote(int testnoteid, bool activate)
        {
            Logger.Log().Info(string.Format("{0}", MethodBase.GetCurrentMethod().Name));
            var allrows = GetTestNoteQuery(testnoteid);

            if (allrows != null)
            {
                allrows.Active = activate;
                _db.SubmitChanges();
            }
        }

        public List<TestNote> GetTestNotes(int testid, bool activeonly)
        {
            Logger.Log().Info(string.Format("{0}", MethodBase.GetCurrentMethod().Name));
            var allrows = from rows in _db.TestNotes
                          where rows.TestId == testid && (!activeonly || rows.Active)
                          orderby rows.CreateDate
                          select rows;

            return allrows.ToList();
        }

        public TestNote GetTestNote(int testnoteid)
        {
            return GetTestNoteQuery(testnoteid);
        }

        public TestNote UpdateTestNote(TestNote notedata)
        {
            Logger.Log().Info(string.Format("{0}", MethodBase.GetCurrentMethod().Name));
            var testnote = GetTestNoteQuery(notedata.TestNoteId);

            if (testnote != null)
            {
                testnote.CreatedBy = notedata.CreatedBy;
                testnote.Note = notedata.Note;
                testnote.Active = true;
                testnote.CreateDate = DateTime.Now;
                _db.SubmitChanges();

                return testnote;
            }

            return null;
        }

        public string GetCurrentUser()
        {
            Logger.Log().Info(string.Format("{0}", MethodBase.GetCurrentMethod().Name));
            var c = ServiceSecurityContext.Current;

            if (c != null && c.PrimaryIdentity != null)
                return c.PrimaryIdentity.Name.ToLower();

            return null;
        }

        public bool SetTestOwner(int testenvironmentid, int testid, string newowner, bool force, out string owner)
        {
            Logger.Log().Info(string.Format("{0}", MethodBase.GetCurrentMethod().Name));
            var result = (from rows in _db.SetTestOwner(testid, testenvironmentid, newowner, force)
                         select rows).FirstOrDefault();

            if (result == null)
            {
                owner = null;
                return false;
            }

            owner = result.NewOwner;
            return result.Success.GetValueOrDefault(false);
        }

        public bool ClearTestOwner(int testenvironmentid, int testid, string oldowner, bool force, out string owner)
        {
            Logger.Log().Info(string.Format("{0}", MethodBase.GetCurrentMethod().Name));
            var result = (from rows in _db.ClearTestOwner(testid, testenvironmentid, oldowner, force)
                          select rows).FirstOrDefault();

            if (result == null)
            {
                owner = null;
                return false;
            }

            owner = result.NewOwner;
            return result.Success.GetValueOrDefault(false);
        }

		public TestRunTask GetTestRunTaskData(long testruntaskid)
        {
            Logger.Log().Info(string.Format("{0}", MethodBase.GetCurrentMethod().Name));
            var allrows = from rows in _db.TestRunTasks
                          where rows.TestRunTaskId == testruntaskid
                          select rows;

            return allrows.FirstOrDefault();
        }

        public void UpdateTestEnvironment(int testenvironmentid, string name, string description)
        {
            Logger.Log().Info(string.Format("{0}", MethodBase.GetCurrentMethod().Name));
            var row = (from rows in _db.TestEnvironments
                       where rows.TestEnvironmentId == testenvironmentid
                       select rows).FirstOrDefault();

            if (row == null)
                return;

            row.Description = description;
            row.EnvironmentName = name;
            _db.SubmitChanges();
        }

        public int AddTestEnvironment(string name, string description)
        {
            Logger.Log().Info(string.Format("{0}", MethodBase.GetCurrentMethod().Name));
            int? testenvironmentid = null;
            _db.GetTestEnvironmentId(name, ref testenvironmentid);

            if (!testenvironmentid.HasValue)
                return 0;

            UpdateTestEnvironment(testenvironmentid.Value, name, description);

            return testenvironmentid.Value;
        }

        public List<GetEnvironmentSettingResult> GetEnvironmentSettings(int testenvironmentid)
        {
            Logger.Log().Info(string.Format("{0}", MethodBase.GetCurrentMethod().Name));
            return _db
                .GetEnvironmentSetting(testenvironmentid, null)
                .OrderBy(es => es.SettingName)
                .ToList();
        }

        public GetEnvironmentSettingResult GetEnvironmentSettingValue(int testenvironmentid, string settingname)
        {
            Logger.Log().Info(string.Format("{0}", MethodBase.GetCurrentMethod().Name));
            return _db.GetEnvironmentSetting(testenvironmentid, settingname).FirstOrDefault();
        }

        public void SetEnvironmentSettingValue(int testenvironmentid, string settingname, string settingvalue)
        {
            Logger.Log().Info(string.Format("{0}", MethodBase.GetCurrentMethod().Name));
            if (settingvalue == null)
                settingvalue = string.Empty;

            _db.AddEnvironmentSetting(testenvironmentid, settingname.Trim(), settingvalue.Trim());
        }

        public void DeleteEnvironmentSetting(int testenvironmentid, string settingname)
        {
            Logger.Log().Info(string.Format("{0}", MethodBase.GetCurrentMethod().Name));
            _db.DeleteEnvironmentSetting(testenvironmentid, settingname);
        }

        /// <summary>
        /// Gets a list of existing remote control capabilities
        /// </summary>
        /// <returns>The list of capabilities</returns>
        public List<RemoteControlCapability> GetAllRemoteControlCapabilities()
        {
            Logger.Log().Info(string.Format("{0}", MethodBase.GetCurrentMethod().Name));
            return _db.RemoteControlCapabilities.ToList();
        }

        /// <summary>
        /// Gets a list of remote control capabilities for a host
        /// </summary>
        /// <returns>The list of capabilities</returns>
        public List<RemoteControlCapability> GetRemoteControlCapabilities(string hostname)
        {
            Logger.Log().Info(string.Format("{0}", MethodBase.GetCurrentMethod().Name));
            var host = _db.RemoteControlHostLists.Where(rchl => rchl.HostName == hostname).FirstOrDefault();
            if (host == null)
                return new List<RemoteControlCapability>();

            return _db.RemoteControlHostCapabilities
                .Where(rchc => rchc.RemoteControlHostListId == host.RemoteControlHostListId)
                .Join(_db.RemoteControlCapabilities,
                      rchc => rchc.RemoteControlCapabilityCode,
                      rcc => rcc.RemoteControlCapabilityCode,
                      (rchc, rcc) => rcc)
                .ToList();
        }

        /// <summary>
        /// Adds a list of remote control capabilities to the system
        /// </summary>
        /// <param name="capabilities">The list of new (or existings) capabilities</param>
        public void AddRemoteControlCapabilities(List<RemoteControlCapability> capabilities)
        {
            Logger.Log().Info(string.Format("{0}", MethodBase.GetCurrentMethod().Name));

            var changesmade = false;
            foreach (var ccap in capabilities)
            {
                var cap = ccap;
                if (!_db.RemoteControlCapabilities.Any(rcc => rcc.RemoteControlCapabilityCode == cap.RemoteControlCapabilityCode))
                {
                    _db.RemoteControlCapabilities.InsertOnSubmit(ccap);
                    changesmade = true;
                }
            }

            if (changesmade)
                _db.SubmitChanges();
        }

        /// <summary>
        /// Gets the next available remote control host from the Quail pool
        /// </summary>
        /// <param name="testname">the name of the test</param>
        /// <param name="capabilities">list of needed capabilities</param>
        /// <returns>
        /// The new host or NULL if no host is available
        /// </returns>
        public GetNextRemoteControlHostResult GetNextRemoteControlHost(string testname, string[] capabilities)
        {
            Logger.Log().Info(string.Format("{0}", MethodBase.GetCurrentMethod().Name));
            string caps = null;

            if (capabilities != null && capabilities.Length > 0)
            {
                Array.ForEach(capabilities, cap =>
                {
                    if (caps == null)
                        caps = cap;
                    else
                        caps += "," + cap;
                });
            }

            return _db.GetNextRemoteControlHost(testname, caps).FirstOrDefault();
        }

        /// <summary>
        /// Releases a remote control host so it can be used for another test.  Use the hostkey returned from GetNextRemoteControlHost
        /// </summary>
        /// <param name="hostkey">The hostkey returned from GetNextRemoteControlHost</param>
        public void ReleaseRemoteControlHost(int hostkey)
        {
            Logger.Log().Info(string.Format("{0}", MethodBase.GetCurrentMethod().Name));
            _db.ReleaseRemoteControlHost(hostkey);
        }


        /// <summary>
        /// Returns a list of load testing groups
        /// </summary>
        /// <returns>List of load test groups</returns>
        public List<string> GetLoadTestGroups()
        {
            return _db.LoadTestDatas
                .Select(ltd => ltd.DataGroup)
                .Distinct()
                .OrderBy(ltd => ltd)
                .ToList();
        }

        /// <summary>
        /// Returns a list of load testing data
        /// </summary>
        /// <param name="dataGroup">Specify a specific data group (optional)</param>
        /// <returns>List of LoadTestData objects</returns>
        public List<LoadTestData> GetLoadTestData(string dataGroup)
        {
            Logger.Log().Info(string.Format("{0}", MethodBase.GetCurrentMethod().Name));
            var result =
                _db.LoadTestDatas
                    .Where(ltd => dataGroup == null || ltd.DataGroup == dataGroup)
                    .Where(ltd => ltd.IsActive)
                    .ToList();

            return result;
        }

        /// <summary>
        /// Starts load test run
        /// </summary>
        /// <param name="testRunId">The TestRunId for the load test</param>
        /// <param name="testEnvironmentId">The TestEnvironmentId for the load test (optional if testRunId is provided)</param>
        /// <param name="numTests">the number of individual tests run</param>
        /// <param name="numThreads">the number of threads used</param>
        /// <returns>The LoadTestId - use in FinishLoadTest</returns>
        public int CreateLoadTest(int? testRunId, int? testEnvironmentId, int? numTests, int? numThreads)
        {
            Logger.Log().Info(string.Format("{0}", MethodBase.GetCurrentMethod().Name));
            if (testRunId.HasValue)
            {
                testEnvironmentId = _db.TestRuns
                    .Where(tr => tr.TestRunId == testRunId.Value)
                    .Select(tr => tr.TestEnvironmentId)
                    .FirstOrDefault();
            }

            if (!testEnvironmentId.HasValue)
                throw new ArgumentNullException("testEnvironmentId");

            var lt = new LoadTest
            {
                StartDate = DateTime.Now,
                TestEnvironmentId = testEnvironmentId.Value,
                TestRunId = testRunId,
                NumTests = numTests,
                NumThreads = numThreads
            };

            _db.LoadTests.InsertOnSubmit(lt);
            _db.SubmitChanges();

            return lt.LoadTestId;
        }

        public void AddLoadTestUrlRun(int loadTestId, int loadTestDataId, int[] msToFirstByte, int[] msToLastByte, int[] size, int[] statusCode, DateTime?[] startDate)
        {
            Logger.Log().Info(string.Format("{0}", MethodBase.GetCurrentMethod().Name));
            var thelt = _db.LoadTests.Where(lt => lt.LoadTestId == loadTestId).FirstOrDefault();

            if (thelt == null)
                return;

            if (msToFirstByte.Length != msToLastByte.Length ||
                msToLastByte.Length != size.Length ||
                size.Length != statusCode.Length ||
                statusCode.Length != startDate.Length)
            {
                throw new ArgumentOutOfRangeException();
            }

            var ltu = thelt.LoadTestUrls.Where(ltuu => ltuu.LoadTestDataId == loadTestDataId).FirstOrDefault();

            if (ltu == null)
            {
                ltu = new LoadTestUrl
                          {
                              LoadTestDataId = loadTestDataId,
                              LoadTestId = loadTestId
                          };

                thelt.LoadTestUrls.Add(ltu);

                _db.SubmitChanges();
            }

            for (var i = 0; i < msToFirstByte.Length; i++)
            {
                _db.LoadTestUrlRuns.InsertOnSubmit(new LoadTestUrlRun
                                                       {
                                                           LoadTestUrlId = ltu.LoadTestUrlId,
                                                           FirstByte = msToFirstByte[i],
                                                           LastByte = msToLastByte[i],
                                                           Size = size[i],
                                                           StatusCode = statusCode[i],
                                                           StartTime = startDate[i]
                                                       });
            }

            _db.SubmitChanges();
        }

        public void FinishLoadTest(int loadTestId)
        {
            Logger.Log().Info(string.Format("{0}", MethodBase.GetCurrentMethod().Name));

            var thelt = _db.LoadTests.Where(lt => lt.LoadTestId == loadTestId).FirstOrDefault();

            if (thelt != null)
            {
                thelt.FinishDate = DateTime.Now;
                _db.SubmitChanges();
            }
        }

        public List<LoadTestRunData> GetLoadTestRun(string dataGroup, string url, int testEnvironmentId, DateTime? startDate, DateTime? endDate)
        {
            Logger.Log().Info(string.Format("{0}", MethodBase.GetCurrentMethod().Name));

            // ltds is the load test data we want
            var ltds = _db.LoadTestDatas
                .Where(ltd => dataGroup == null || ltd.DataGroup == dataGroup)
                .Where(ltd => url == null || ltd.Url == url);

            // ts is the load tests we want
            var ts = _db
                .LoadTests
                .Where(lt => lt.TestEnvironmentId == testEnvironmentId)
                .Where(lt => !startDate.HasValue || lt.StartDate >= startDate.GetValueOrDefault())
                .Where(lt => !endDate.HasValue || lt.StartDate < endDate.GetValueOrDefault())
                .SelectMany(lt => lt.LoadTestUrls.Select(ltu => new
                                                                    {
                                                                        lt.LoadTestId,
                                                                        lt.StartDate,
                                                                        lt.FinishDate,
                                                                        lt.TestRunId,
                                                                        ltu.LoadTestDataId,
                                                                        ltu.LoadTestUrlId,
                                                                        ltu.LoadTestUrlRuns
                                                                    }));



            // data is the join of the ts ans ltds we want
            var data =
                ltds.Join(
                    ts,
                    ltd => ltd.LoadTestDataId,
                    lt => lt.LoadTestDataId,
                    (ltd, lt) => new
                                     {
                                         ltd.Url,
                                         lt.TestRunId,
                                         lt.StartDate,
                                         lt.FinishDate,
                                         lt.LoadTestUrlRuns
                                     })
                    .ToList();

            // result is the proxied data we want
            var result =
                data.Select(d => new LoadTestRunData
                                     {
                                         Url = d.Url,
                                         StartTime = d.StartDate,
                                         FinishTime = d.FinishDate,
                                         Details = d
                                             .LoadTestUrlRuns
                                             .Select(ltur => new LoadTestRunDetail
                                                                 {
                                                                     MsToFirstByte =
                                                                         ltur.FirstByte,
                                                                     MsTotal = ltur.LastByte,
                                                                     Size = ltur.Size,
                                                                     StartTime = ltur.StartTime.GetValueOrDefault(d.StartDate),
                                                                     StatusCode = ltur.StatusCode
                                                                 })
                                             .ToList()

                                     })
                    .ToList();

            return result;

            return new List<LoadTestRunData>
                       {
                           new LoadTestRunData
                               {
                                   Url = "http://uat.js.snagqa.com/default.aspx",
                                   Details = new[]
                                                 {
                                                     new LoadTestRunDetail
                                                         {
                                                             MsToFirstByte = 1,
                                                             MsTotal = 2,
                                                             Size = 3,
                                                             StartTime = DateTime.Now,
                                                             StatusCode = 200
                                                         }
                                                 }.ToList()
                               }

                       };
        }


        public List<LoadTestRunData> GetLoadTestRuns(string dataGroup, string url, int testEnvironmentId, DateTime? startDate, DateTime? endDate, int? numTests, int? numThreads)
        {
            Logger.Log().Info(string.Format("{0}", MethodBase.GetCurrentMethod().Name));

            // ltds is the load test data we want
            var ltds = _db.LoadTestDatas
                .Where(ltd => dataGroup == null || ltd.DataGroup == dataGroup)
                .Where(ltd => url == null || ltd.Url == url);

            // ts is the load tests we want
            var ts = _db
                .LoadTests
                .Where(lt => lt.TestEnvironmentId == testEnvironmentId)
                .Where(lt => !startDate.HasValue || lt.StartDate >= startDate.GetValueOrDefault())
                .Where(lt => !endDate.HasValue || lt.StartDate < endDate.GetValueOrDefault())
                .Where(lt => !numTests.HasValue || numTests == 0 || lt.NumTests == numTests)
                .Where(lt => !numThreads.HasValue || numThreads == 0 || lt.NumThreads == numThreads)
                .SelectMany(lt => lt.LoadTestUrls.Select(ltu => new
                {
                    lt.LoadTestId,
                    lt.StartDate,
                    lt.FinishDate,
                    lt.TestRunId,
                    ltu.LoadTestDataId,
                    ltu.LoadTestUrlId,
                    ltu.LoadTestUrlRuns
                }));

            // data is the join of the ts ans ltds we want
            var data =
                ltds.Join(
                    ts,
                    ltd => ltd.LoadTestDataId,
                    lt => lt.LoadTestDataId,
                    (ltd, lt) => new
                    {
                        ltd.Url,
                        lt.TestRunId,
                        lt.StartDate,
                        lt.FinishDate,
                        lt.LoadTestUrlRuns
                    })
                    .ToList();

            // result is the proxied data we want
            var result =
                data.Select(d => new LoadTestRunData
                {
                    Url = d.Url,
                    StartTime = d.StartDate,
                    FinishTime = d.FinishDate,
                    Details = d
                        .LoadTestUrlRuns
                        .Select(ltur => new LoadTestRunDetail
                        {
                            MsToFirstByte =
                                ltur.FirstByte,
                            MsTotal = ltur.LastByte,
                            Size = ltur.Size,
                            StartTime = ltur.StartTime.GetValueOrDefault(d.StartDate),
                            StatusCode = ltur.StatusCode
                        })
                        .ToList()

                })
                    .ToList();

            return result;
        }

		public void DeleteTestRunTask(long testRunTaskId)
        {
            Logger.Log().Info(string.Format("{0}({1})", MethodBase.GetCurrentMethod().Name, testRunTaskId));
            _db.DeleteTestRunTask(testRunTaskId);
        }

        public void FinishTestRun(int testRunId, int runStatusId, DateTime runFinishDate)
        {
            Logger.Log().Info(string.Format("{0}({1},{2},{3})", MethodBase.GetCurrentMethod().Name,
                                            testRunId, runStatusId, runFinishDate));
            _db.FinishTestRun(testRunId, runStatusId, runFinishDate);
        }

		public void FinishTestRunTask(long testRunTaskId, int taskStatusId, DateTime taskFinishDate)
        {
            Logger.Log().Info(string.Format("{0}({1},{2},{3})", MethodBase.GetCurrentMethod().Name,
                                            testRunTaskId, taskStatusId, taskFinishDate));
            _db.FinishTestRunTask(testRunTaskId, taskStatusId, taskFinishDate);
        }

        public int StartTestRun(string testName, string environmentName, string libraryName, int runStatusId, string testClassName, DateTime runStartDate, int? testLibraryRunId)
        {
            Logger.Log().Info(string.Format("{0}({1},{2},{3},{4},{5},{6},{7})", MethodBase.GetCurrentMethod().Name,
                                            testName, environmentName, libraryName, runStatusId, testClassName,
                                            runStartDate,
                                            testLibraryRunId.HasValue ? testLibraryRunId.Value.ToString(CultureInfo.InvariantCulture) : "NULL"));
            int? testrunid = 0;
            _db.StartTestRun(ref testrunid, testName, environmentName, libraryName, runStatusId, testClassName,
                             runStartDate, testLibraryRunId);

            return testrunid.GetValueOrDefault();
        }

		public long InsertTestRunTaskLogEntry(long testRunTaskId, int entryStatusId, string url, string entry, DateTime entryCreateDate)
        {
            Logger.Log().Info(string.Format("{0}({1},{2},{3},{4},{5})", MethodBase.GetCurrentMethod().Name,
                                            testRunTaskId, entryStatusId, url, entry, entryCreateDate));
			long? testruntasklogentryid = 0;

            _db.InsertTestRunTaskLogEntry(ref testruntasklogentryid, testRunTaskId, entryStatusId, url, entry, entryCreateDate);

            return testruntasklogentryid.GetValueOrDefault();
        }

		public long StartTestRunTask(long? parentTestRunTaskId, int testRunId, string taskName, int taskStatusId, DateTime taskStartDate)
        {
            Logger.Log().Info(string.Format("{0}({1},{2},{3},{4},{5})", MethodBase.GetCurrentMethod().Name,
                                            parentTestRunTaskId.HasValue ? parentTestRunTaskId.Value.ToString(CultureInfo.InvariantCulture) : "NULL",
                                            testRunId, taskName, taskStatusId, taskStartDate));
			long? testruntaskid = 0;
            _db.StartTestRunTask(ref testruntaskid, parentTestRunTaskId, testRunId, taskName, taskStatusId, taskStartDate);

            return testruntaskid.GetValueOrDefault();
        }

		public long InsertTestRunTaskAttribute(long testRunTaskId, string attributeName, string attributeValue)
        {
            Logger.Log().Info(string.Format("{0}({1},{2},{3})", MethodBase.GetCurrentMethod().Name, testRunTaskId,
                                            attributeName, attributeValue));
            long? testruntaskattributeid = 0;
            _db.InsertTestRunTaskAttribute(ref testruntaskattributeid, testRunTaskId, attributeName, attributeValue);

            return testruntaskattributeid.GetValueOrDefault();
        }

        /// <summary>
        /// Returns a list of Test Monitor Types
        /// </summary>
        /// <returns>List of TestMonitorType objects</returns>
        public List<TestMonitorType> GetTestMonitorTypes()
        {
            Logger.Log().Info(string.Format("{0}", MethodBase.GetCurrentMethod().Name));
            List<TestMonitorType> list = _db.TestMonitorTypes.ToList();
            return list;
        }


		public List<TestMonitor> GetTestMonitors(int environmentId, int monitorTypeId, int taskStatusId, string message, int maxResults)
        {
            // List<TestMonitor> testMonitors = null;
            if (monitorTypeId == -1)
            {
                var tempList = _db.ViewTestMonitorActives
                    .Where
                    (
                        testMonitor =>
                        testMonitor.TestEnvironmentId == environmentId &&
                        (string.IsNullOrEmpty(message) || testMonitor.Message.Contains(message))
                    )
                    .OrderByDescending(testMonitor => testMonitor.CreateDate)
                    .ToList();
                
                //wasn't able to create new Entity object within linq query
                return
                    tempList.Select(
                        viewTestMonitorActive =>
                        new TestMonitor
                            {
                                CreateDate = viewTestMonitorActive.CreateDate,
                                Message = viewTestMonitorActive.Message,
                                TaskStatusId = viewTestMonitorActive.TaskStatusId,
                                TestMonitorTypeId =
                                    viewTestMonitorActive.TestMonitorTypeId,
                                TestRunId = viewTestMonitorActive.TestRunId
                            })
                        .ToList();
            }
            else
            {
                var tempList = _db.TestMonitors
                    .Join(_db.TestRuns, tm => tm.TestRunId, tr => tr.TestRunId,
                          (tm, tr) => new {TestMonitor = tm, TestRun = tr})
                    .Where
                    (
                        row =>
                        row.TestMonitor.TestMonitorTypeId == monitorTypeId &&
                        (taskStatusId == -1 || row.TestMonitor.TaskStatusId == taskStatusId) &&
                        (string.IsNullOrEmpty(message) || row.TestMonitor.Message.Contains(message)) &&
                        row.TestRun.TestEnvironmentId == environmentId
                    )
                    .OrderByDescending(row => row.TestMonitor.CreateDate)
                    .Take(maxResults)
                    .Select
                    (
                        data => new
                                    {
                                        TestMonitorTypeId = data.TestMonitor.TestMonitorTypeId,
                                        TestRunId = data.TestMonitor.TestRunId,
                                        CreateDate = data.TestMonitor.CreateDate,
                                        Message = data.TestMonitor.Message,
                                        TaskStatusId = data.TestMonitor.TaskStatusId
                                    }
                    )
                    .ToList();

                //wasn't able to create new Entity object within linq query
                return
                    tempList
                        .Select
                        (
                            viewTestMonitorActive =>
                            new TestMonitor
                                {
                                    CreateDate = viewTestMonitorActive.CreateDate,
                                    Message = viewTestMonitorActive.Message,
                                    TaskStatusId = viewTestMonitorActive.TaskStatusId,
                                    TestMonitorTypeId = viewTestMonitorActive.TestMonitorTypeId,
                                    TestRunId = viewTestMonitorActive.TestRunId
                                }
                        )
                        .ToList();
            }
        }


        public void InsertTestMonitor(int testMonitorTypeId, int taskStatusId, DateTime createDate, int testRunId, string message)
        {
            Logger.Log().Info(string.Format("{0}", MethodBase.GetCurrentMethod().Name));

            _db.TestMonitors.InsertOnSubmit(new TestMonitor
            {
                TestMonitorTypeId = testMonitorTypeId,
                TaskStatusId = taskStatusId,
                CreateDate = createDate,
                TestRunId = testRunId,
                Message = message,
            });

            _db.SubmitChanges();
        }

        public void InsertNetworkTrafficUrl(int testRunId, string browserUrl, int browserUrlCounter, string remoteControlUrl, int msUntilReady, List<NetworkTrafficData> traffic)
        {
            Logger.Log().Info(string.Format("{0}", MethodBase.GetCurrentMethod().Name));

            var trafficurlid = _db.InsertNetworkTrafficUrl(testRunId, browserUrl, browserUrlCounter, remoteControlUrl, msUntilReady);

            foreach (var item in traffic)
            {
                var trafficid = _db.InsertNetworkTraffic(trafficurlid, item.Url, item.Start, item.TimeInMillis, item.StatusCode, item.Method, item.Bytes);

                foreach (var value in item.ResponseHeaders)
                    _db.InsertNetworkTrafficHeader(trafficid, value.Name, value.Value);
            }
        }

        public int? StartTestLibraryRun(string environmentName, string libraryName, DateTime startDate, string host)
        {
            Logger.Log().Info(string.Format("{0}({1},{2},{3},{4})", MethodBase.GetCurrentMethod().Name, environmentName, libraryName, startDate, host));

            var result = _db.StartTestLibraryRun(environmentName, libraryName, startDate, host).ToList().FirstOrDefault();

            if (result != null && result.TestLibraryRunId.HasValue)
                return (int) result.TestLibraryRunId;

            return null;
        }

        /// <summary>
        /// Returns the number of tests currently running for a given environment
        /// </summary>
        /// <param name="testEnvironmentId">The testEnvironmentId you'd like to check</param>
        /// <returns></returns>
        public int GetCurrentActiveTestCount(int testEnvironmentId)
        {
            return GetRunningTests(testEnvironmentId).Count();
        }

        /// <summary>
        /// Returns the active tests currently running for a given environment
        /// </summary>
        /// <param name="testEnvironmentId">The testEnvironmentId you'd like to check</param>
        /// <returns></returns>
        public List<ViewTestRun> GetCurrentActiveTests(int testEnvironmentId)
        {
            return GetRunningTests(testEnvironmentId).ToList();
        }


        /// <summary>
        /// Adds a list of revisions to a test library run
        /// </summary>
        /// <param name="revisions"></param>
        public void AddTestLibraryRunRevisions(IEnumerable<TestLibraryRunTeamRevision> revisions)
        {
            _db.TestLibraryRunTeamRevisions.InsertAllOnSubmit(revisions);
            _db.SubmitChanges();
        }

        /// <summary>
        /// Inserts a new pre-created object
        /// </summary>
        /// <param name="testEnvironmentId">the test environment id</param>
        /// <param name="objectType">The type of object</param>
        /// <param name="objectKey">The key to the object</param>
        public void InsertPreCreatedObject(int testEnvironmentId, string objectType, string objectKey)
        {
            _db.InsertPreCreatedObject(testEnvironmentId, objectType, objectKey);
        }

        /// <summary>
        /// Gets a pre-created object key
        /// </summary>
        /// <param name="testEnvironmentId">the test environment id</param>
        /// <param name="objectType">The type of the object</param>
        /// <returns>A key to the object</returns>
        public string GetPreCreatedObject(int testEnvironmentId, string objectType)
        {
            var result = _db.GetPreCreatedObject(testEnvironmentId, objectType).ToList();

            return result.Any()
                       ? result.Select(v => v.ObjectKey).First()
                       : null;
        }

        /// <summary>
        /// Gets count of available pre-created object keys as this instant
        /// </summary>
        /// <param name="testEnvironmentId">the test environment id</param>
        /// <param name="objectType">The type of the object</param>
        /// <returns>A key to the object</returns>
        public int GetPreCreatedObjectCount(int testEnvironmentId, string objectType)
        {
            var result = _db.GetPreCreatedObjectCount(testEnvironmentId, objectType).ToList();

            return result.Any()
                       ? result.Select(v => v.Count.GetValueOrDefault()).First()
                       : 0;
        }

        /// <summary>
        /// Removes all pre-created objects by the type
        /// </summary>
        /// <param name="testEnvironmentId">the test environment id</param>
        /// <param name="objectType">The type of the object</param>
        public void ClearPreCreatedObjects(int testEnvironmentId, string objectType)
        {
            _db.ClearPreCreatedObjects(testEnvironmentId, objectType);
        }


        public List<ViewFailedTestPercentagesOverallResult> GetFailedTestPercentagesOverall(DateTime? from, DateTime? to)
        {
            return _db.ViewFailedTestPercentagesOverall(from, to).ToList();
        }

        public List<ViewFailedTestPercentage> GetFailedTestPercentages(string environment, string libraryname, string fulltestname, DateTime? from, DateTime ?to)
        {
            var query = _db.ViewFailedTestPercentages as IQueryable<ViewFailedTestPercentage>;

            if (!string.IsNullOrEmpty(environment))
                query = query.Where(ftp => ftp.EnvironmentName == environment);

            if (!string.IsNullOrEmpty(libraryname))
                query = query.Where(ftp => ftp.LibraryName == libraryname);

            if (!string.IsNullOrEmpty(fulltestname))
                query = query.Where(ftp => ftp.FullTestName == fulltestname);

            if (from.HasValue)
            {
                if (!to.HasValue)
                    to = from.Value;

                var dtfrom = from.Value;
                var dtto = to.Value;

                query = query.Where(ftp => ftp.TestDate >= dtfrom && ftp.TestDate <= dtto);
            }

            return query.ToList();
        }


        /// <summary>
        /// Get record(s) from AsyncTask table by name and status.
        /// </summary>
        /// <param name="testName">String representing AsyncTask record name.</param>
        /// <param name="statusId">Int representing AsyncTask record status.</param>
        public List<AsyncTask> GetAsyncTasksByName(string testName, int statusId)
        {
            Logger.Log().Info(string.Format("{0}", MethodBase.GetCurrentMethod().Name));
            return GetAsyncTasks(testName, statusId).ToList();
        }

        /// <summary>
        /// Get all records from the AsyncTask table with the given status id in the 
        /// test environment.
        /// </summary>
        /// <param name="statusId">Int representing AysncTask record status.</param>
        /// <param name="environmentId">The environment id to retrieve taks for.</param>
        /// <returns>A list of <see cref="AsyncTask"/> objects.</returns>
        public List<AsyncTask> GetAllAsyncTasks(int statusId, int environmentId)
        {
            Logger.Log().Info(string.Format("{0}", MethodBase.GetCurrentMethod().Name));
            return _db.AsyncTasks.Where(
                    t => t.StatusId == statusId &&
                    _db.TestRuns.Any(r => r.TestRunId == t.TestRunId && r.TestEnvironmentId == environmentId))
                .ToList();
        }


        /// <summary>
        /// Get count of records from AsyncTask table by name and status.
        /// </summary>
        /// <param name="testName">String representing AsyncTask record name.</param>
        /// <param name="statusId">Int representing AsyncTask record status.</param>
        public int GetAsyncTasksByNameCount(string testName, int statusId)
        {
            Logger.Log().Info(string.Format("{0}", MethodBase.GetCurrentMethod().Name));
            return GetAsyncTasks(testName, statusId).Count();
        }


        /// <summary>
        /// Insert record into AsyncTask table.
        /// </summary>
        /// <param name="asyncTask">Object representing AsyncTask record.</param>
        public int InsertAsyncTask(AsyncTask asyncTask)
        {
            Logger.Log().Info(string.Format("{0}", MethodBase.GetCurrentMethod().Name));

            _db.AsyncTasks.InsertOnSubmit(asyncTask);
            _db.SubmitChanges();

            return asyncTask.AsyncTaskId;
        }


        /// <summary>
        /// Update record in AsyncTask table.
        /// </summary>
        /// <param name="asyncTask">Object representing AsyncTask record. Must have AsyncTaskID.</param>
        public void UpdateAsyncTask(AsyncTask asyncTask)
        {
            Logger.Log().Info(string.Format("{0}", MethodBase.GetCurrentMethod().Name));

            if (asyncTask.AsyncTaskId > 0)
            {
                var record =
                    (from row in _db.AsyncTasks
                     where row.AsyncTaskId == asyncTask.AsyncTaskId
                     select row).FirstOrDefault();

                if (record != null)
                {
                    record.TestRunId = asyncTask.TestRunId;
                    record.TestRunTaskId = asyncTask.TestRunTaskId;
                    record.TestName = asyncTask.TestName;
                    record.TestData = asyncTask.TestData;
                    record.StatusId = asyncTask.StatusId;
                    record.DateExpires = asyncTask.DateExpires;

                    _db.SubmitChanges();
                }
            }
        }


        /// <summary>
        /// Update record in TestRun table. NOTE: This is not a generic update method as it currently only supports updating the status.
        ///  TODO: Find out if this should support updating all columns, and how to best do this conditionally with LINQ.
        /// </summary>
        /// <param name="testRun">Object representing TestRun record. Must have TestRunId.</param>
        public void UpdateTestRun(TestRun testRun)
        {
            Logger.Log().Info(string.Format("{0}", MethodBase.GetCurrentMethod().Name));

            if (testRun.TestRunId > 0)
            {
                var record =
                    (from row in _db.TestRuns
                     where row.TestRunId == testRun.TestRunId
                     select row).FirstOrDefault();

                if (record != null)
                {
                    record.RunStatusId = testRun.RunStatusId;

                    _db.SubmitChanges();
                }
            }
        }


        /// <summary>
        /// Update record in TestRunTask table. NOTE: This is not a generic update method as it currently only supports updating the status.
        ///  TODO: Find out if this should support updating all columns, and how to best do this conditionally with LINQ.
        /// </summary>
        /// <param name="testRunTask">Object representing TestRunTask record. Must have TestRunId and TaskName.</param>
        public void UpdateTestRunTask(TestRunTask testRunTask)
        {
            Logger.Log().Info(string.Format("{0}", MethodBase.GetCurrentMethod().Name));

            if ((testRunTask.TestRunId > 0) && (testRunTask.TestRunTaskId > 0))
            {
                var record =
                    (from row in _db.TestRunTasks
                     where row.TestRunId == testRunTask.TestRunId
                        && row.TestRunTaskId == testRunTask.TestRunTaskId
                     select row).FirstOrDefault();

                if (record != null)
                {
                    record.TaskStatusId = testRunTask.TaskStatusId;

                    _db.SubmitChanges();
                }
            }
        }



        /// <summary>
        /// Private method to get all record(s) from AsyncTask table by name and status.
        /// </summary>
        /// <param name="testName">String representing AsyncTask record name.</param>
        /// <param name="statusId">Int representing AsyncTask record status.</param>
        private IQueryable<AsyncTask> GetAsyncTasks(string testName, int statusId)
        {
            return _db.AsyncTasks
                .Where(at => at.TestName == testName && 
                             at.StatusId == statusId);
        }

        private IQueryable<ViewTestRun> GetRunningTests(int testEnvironmentId)
        {
            var dtnow = DateTime.Now - new TimeSpan(0, 30, 0);

            return _db.ViewTestRuns
                .Where(t => t.TestEnvironmentId == testEnvironmentId &&
                            t.RunStartDate >= dtnow &&
                            t.RunFinishDate == null);
        }

        private TestNote GetTestNoteQuery(int testnoteid)
        {
            var allrows = (from rows in _db.TestNotes
                          where rows.TestNoteId == testnoteid
                          select rows).FirstOrDefault();

            return allrows;
        }

        private IOrderedQueryable<TestEnvironment> GetEnvironmentRows()
        {
            return from rows in _db.TestEnvironments
                   orderby rows.Description
                   select rows;
        }
    }
}